package com.group.dataroom;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.sql.DataSource;

import org.apache.catalina.connector.Response;


import com.group.board.BoardData;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class DataMgr {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	private DataSource ds;
	
	int tot =0;
	int plist = 5; //페이지 당 출력 레코드
	int pageSu = 0;	
	
//	public int getPlist(int plist){
//		return plist;
//	}
//	
	public void setPlist(int plist) {
		this.plist = plist;
	}

	public int getPageSu(){	//	페이지 수 구하기
		pageSu = tot / plist;
		if(tot % plist > 0){
			pageSu++;
		}
		return pageSu;
	}
	
	public DataMgr(){
		try{
			//pooling 처리 : dbcp
			Context context = new InitialContext();
			ds = (DataSource)context.lookup("java:comp/env/jdbc/Oracle10g");
		}catch(Exception e){
			System.out.println("연결 오류:" + e.getMessage());
		}
	}//dataMgr 끝
	
	public ArrayList<DataBean> getDataBean(String word, String type, int page){
		String sql = "select * from data_room";
		ArrayList<DataBean> list = new ArrayList<>();
		try {
			conn = ds.getConnection();
			
			if(word == null){
				pstmt = conn.prepareStatement(sql);
			}else{ //	검색인 경우
				sql += " where " + type + " like ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+word+"%");
			}
			
			rs = pstmt.executeQuery();
			
			for (int i = 0; i < (page -1) * plist; i++) {
				rs.next();
			}
			
			int k = 0;			
			while(rs.next() && k < plist){
				
				DataBean bean = new DataBean();
				bean.setNo(rs.getString("no"));
				bean.setUser_name(rs.getString("user_name"));
				bean.setFilename(rs.getString("filename"));
				bean.setFdata(rs.getString("fdata"));
				bean.setCont(rs.getString("cont"));
				bean.setWritedate(rs.getString("writedate"));
				list.add(bean);
				
				k++;
			}
		} catch (Exception e) {
			System.out.println("getDataBean 오류:" + e.getMessage());
		}finally{
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(conn != null) conn.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list;
	}//자료들어옴
	
	
	
	
	public boolean insertProduct(HttpServletRequest request){
		boolean b = false;
		
		try{
			String uploadDir ="C:/data";
			MultipartRequest multi = new MultipartRequest(request, uploadDir,5*1024*1024,"utf-8",new DefaultFileRenamePolicy());
			String sql = "insert into data_room(no,user_name,filename,cont,writedate,fdata) values(data_no.nextval, ?, ?, ?,sysdate,?)";
			
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, multi.getParameter("user_name").trim());
			pstmt.setString(2, multi.getParameter("filename").trim());
			pstmt.setString(3, multi.getParameter("cont").trim());
			if(multi.getFilesystemName("fdata")==null){
				pstmt.setString(4, "ready.gif");				
			}else{
				pstmt.setString(4, multi.getFilesystemName("fdata"));
			}

			int count = pstmt.executeUpdate();
			if(count > 0)b = true;
		}catch(Exception e){
			System.out.println("insertProduct()오류" + e.getMessage());
		}finally{ //자원은 반납한다~!
			try{
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
			}catch(Exception e2){
				
			}
		}	
		return b;
	}
	
	public DataBean getData(String no){
		String sql = "select * from data_room where no=" + no;
		DataBean bean = null;
		
		try{
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				bean = new DataBean();
				bean.setUser_name(rs.getString("user_name"));
				bean.setWritedate(rs.getString("writedate"));
				bean.setFilename(rs.getString("filename"));
				bean.setFdata(rs.getString("fdata"));
				bean.setCont(rs.getString("cont"));
				
			}
		}catch(Exception e){
			System.out.println("getData()오류" + e.getMessage());
		}finally{ //자원은 반납한다~!
			try{
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
			}catch(Exception e2){
				
			}
		}	
		return bean;
	}
	
	public boolean updateProduct(HttpServletRequest request){
		boolean b = false;
		
		try{
		String uploadDir ="C:/data";
		MultipartRequest multi = new MultipartRequest(request, uploadDir,5*1024*1024,"utf-8",new DefaultFileRenamePolicy());
		conn = ds.getConnection();
		if(multi.getFilesystemName("imag")==null){
			String sql = "update data_room set Filename=?, fdata=?, Last_update=?, user_name=?,download_count=? where no =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,multi.getParameter("Filename"));
			pstmt.setString(2,multi.getParameter("fdata"));
			pstmt.setString(3,multi.getParameter("Last_update"));
			pstmt.setString(4,multi.getParameter("user_name"));
			pstmt.setString(5,multi.getParameter("download_count"));
			pstmt.setString(6,multi.getParameter("no"));
		}else{
			String sql = "update data_room set Filename=?, fdata=?, Last_update=?, user_name=?,download_count=? where no =?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,multi.getParameter("Filename"));
			pstmt.setString(2,multi.getParameter("fdata"));
			pstmt.setString(3,multi.getParameter("Last_update"));
			pstmt.setString(4,multi.getParameter("user_name"));
			pstmt.setString(5,multi.getParameter("download_count"));
		}
		
		int count = pstmt.executeUpdate();
		if(count >0)b = true;	
		}catch(Exception e){
			System.out.println("updateProduct()오류" + e.getMessage());
		}finally{ //자원은 반납한다~!
			try{
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
			}catch(Exception e2){
				
			}
		}	
		return b;
	}
	
	
	public boolean deleteProduct(String no){
		boolean b = false;
		try{
			conn = ds.getConnection();
			String sql = "delete from file_main where no=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, no);
			int count = pstmt.executeUpdate();
			if(count >0)b = true;
			
		}catch(Exception e){
			System.out.println("updateProduct()오류" + e.getMessage());
		}finally{ //자원은 반납한다~!
			try{
				if(pstmt != null)pstmt.close();
				if(conn != null)conn.close();
			}catch(Exception e2){
				
			}
		}	
		return b;
	}
	
	
	//레코드 전체 개수
	public void totalList(String word, String type){
		String sql = "select count(*) from data_room";
		try {
			conn = ds.getConnection();
			
			if(word == null){
				pstmt = conn.prepareStatement(sql);
			}else{//검색인 경우. 하단에 where 와 like 앞에 한칸 띄움
				sql += " where " + type + " like ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "%"+word+"%");
			}
			rs = pstmt.executeQuery();
			rs.next();
			tot = rs.getInt(1);
			//System.out.println("전체 레코드 수:" + tot);
		} catch (Exception e) {
			System.out.println("totalList 오류:" + e.getMessage());
		}finally{
			try{
			if(rs !=null)rs.close();	
			if(pstmt !=null)pstmt.close();
			if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
		}catch (Exception e2) {
		
		}
		}
	}
	public int getPagesu(){  //페이지 수 구하기
		pageSu = tot/plist;
		if(tot%plist>0) ++pageSu;
		return pageSu;
	}
	
	
	//수정작업용
		public DataBean getEditData(String no){
			String sql = " select no,title,cont from data_room where no="+no;
			DataBean data = null;
			try {
				conn = ds.getConnection(); 
				pstmt = conn.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()){
					data = new DataBean();
					data.setUser_name(rs.getString("user_name"));
					data.setTitle(rs.getString("title"));
					data.setFdata(rs.getString("fdata"));
					data.setCont(rs.getString("cont"));
				}
			} catch (Exception e) {
				System.out.println("getEditData 오류 : "+e.getMessage());
			}finally{
				try{
				if(rs !=null)rs.close();	
				if(pstmt !=null)pstmt.close();
				if(conn !=null)conn.close(); // 연결후엔 항상 끊어 주어야 한다.
			}catch (Exception e2) {
			}
			}return data;
			}

}//클래스 끝


